﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Configuration;

using BuzzwordBingo.PersistenceLINQ;

namespace BuzzwordBingo.PersistenceLINQ.Test
{
    /// <summary>
    /// Helper class to read/write from database using LINQ
    /// </summary>
    public class DataAccessHelper
    {
        private static BuzzwordBingoDbDataContext DataContext
        {
            get
            {
                return new BuzzwordBingoDbDataContext(ConfigurationManager.ConnectionStrings["BuzzwordBingoDb"].ConnectionString);
            }
        }

        public static BingoEvent GetEvent(int EventId)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                var theEvent = context.BingoEvents.SingleOrDefault(e => e.EventId == EventId);
                var participants =  context.Participants.Where(p => p.EventId == EventId);
                foreach (var p in participants)
                    theEvent.Participants.Add(p);
                return theEvent;
            }
        }

        public static void DeleteEvent(int EventId)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                var theEvent = context.BingoEvents.SingleOrDefault(e => e.EventId == EventId);
                var participants = context.Participants.Where(p => p.EventId == EventId);
                context.Participants.DeleteAllOnSubmit(participants);
                context.BingoEvents.DeleteOnSubmit(theEvent);
                context.SubmitChanges();
            }
        }

        public static void AddEvent(BingoEvent TheEvent)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                context.BingoEvents.InsertOnSubmit(TheEvent);
                context.Participants.InsertAllOnSubmit(TheEvent.Participants);
                context.SubmitChanges();
            }
        }


        public static void AddBuzzCategory(BuzzwordCategory TheCategory, List<Buzzword> BuzzList)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                if (BuzzList != null)
                {
                    foreach (Buzzword bw in BuzzList)
                    {
                        BuzzwordCategory_Buzzword bcl = new BuzzwordCategory_Buzzword();
                        bcl.BuzzwordCategoryId = TheCategory.BuzzwordCategoryId;
                        bcl.BuzzwordId = bw.BuzzwordId;
                        TheCategory.BuzzwordCategory_Buzzwords.Add(bcl);
                    }
                    context.Buzzwords.InsertAllOnSubmit(BuzzList);
                }
                context.BuzzwordCategories.InsertOnSubmit(TheCategory);
                context.SubmitChanges();
            }
        }

        public static void DeleteBuzzCategory(int CategoryId)
        {
            using (BuzzwordBingoDbDataContext context = DataContext)
            {
                var category = context.BuzzwordCategories.SingleOrDefault(c => c.BuzzwordCategoryId == CategoryId);
                var bcl = context.BuzzwordCategory_Buzzwords.Where(l => l.BuzzwordCategoryId == CategoryId);

                List<Buzzword> buzzList = new List<Buzzword>();
                foreach (BuzzwordCategory_Buzzword list in bcl)
                {
                    buzzList.Add(list.Buzzword);
                }

                context.BuzzwordCategory_Buzzwords.DeleteAllOnSubmit(bcl);
                context.Buzzwords.DeleteAllOnSubmit(buzzList);
                context.BuzzwordCategories.DeleteOnSubmit(category);
                context.SubmitChanges();
            }
        }
    }
}
